package org.tinygroup.dbcluster.sample;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.tinygroup.dbcluster.hint.HintManager;

public class Main {

	public static void main(final String[] args) throws SQLException,
			ClassNotFoundException {
		// CHECKSTYLE:ON
		Class.forName("org.tinygroup.dbcluster.jdbc.DbclusterDriver");
		Connection connection = DriverManager
				.getConnection("jdbc:dbcluster://dynamicdriver");
//		 Class.forName("org.postgresql.Driver");
//		 Connection connection = DriverManager
//		 .getConnection(
//		 "jdbc:postgresql://10.20.27.247:5432/db_0?currentSchema=public",
//		 "postgres", "123456");
		try {
			/*
			 * initOrgData(connection); executeUpdate(connection,
			 * "truncate t_org");
			 */
			// System.out.println("清空了表数据");

			
//			  executeUpdate( connection, "CREATE TABLE t_temp (\n" +
//			  "  org_id integer NOT NULL,\n" +
//			  "  org_name varchar(20) DEFAULT NULL,\n" +
//			  "  org_parent integer DEFAULT NULL,\n" +
//			  "  org_detail varchar(1000) DEFAULT NULL\n" + ") ");
//			insertBatch(connection);
			 
			// executeUpdate(connection,"insert into t_org (org_id,org_name) values (2000001,'1212')");
			// executeUpdate(connection,"delete from t_org ");
			// System.out.println("=====");

			DatabaseMetaData metaData = connection.getMetaData();
//			 ResultSet schemas = metaData.getSchemas();
//			 ResultSet schemas = metaData.getCatalogs();
			ResultSet schemas = metaData.getTables(null, "tinydbcluster", "%",
					new String[] { "TABLE" });
			// ResultSet schemas=metaData.getIndexInfo(null, "tinydbcluster",
			// "T_ORG",false,true);
			int num = 0;
			while (schemas.next()) {
				for (int i = 0; i < schemas.getMetaData().getColumnCount(); i++) {
					System.out.println(schemas.getObject(i + 1));
				}
				num++;
			}
			System.out.println("---------" + num + "-----------");
			Statement statement = connection.createStatement();
			long startTime = System.currentTimeMillis();
			 statement.execute("set search_path =db_2");
			// boolean result = statement
			// .execute("CREATE unique INDEX T_ORG_ORG_UNIQUE_NAME_INDEX ON T_ORG (ORG_NAME)");
			ResultSet resultSet = statement
					.executeQuery("select * from t_org order by org_id limit 100 offset 0");
			System.out.println("执行时间："
					+ (System.currentTimeMillis() - startTime));
			startTime = System.currentTimeMillis();
			for (int j = 0; j < 100; j++) {
				if (resultSet.next()) {
					for (int i = 0; i < resultSet.getMetaData()
							.getColumnCount(); i++) {
						System.out.println(resultSet.getObject(i + 1));
					}
					System.out.println("-------------");
				}
			}
			System.out.println("遍历时间："
					+ (System.currentTimeMillis() - startTime));

		} finally {
			if (connection != null) {
				connection.close();
			}
		}

	}

	private static void initOrgData(Connection connection) throws SQLException {
		executeUpdate(
				connection,
				"CREATE TABLE IF NOT EXISTS `t_org` (\n"
						+ "  `org_id` bigint(20) NOT NULL,\n"
						+ "  `org_name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,\n"
						+ "  `org_parent` bigint(20) DEFAULT NULL,\n"
						+ "  `org_detail` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL\n"
						+ ") ");
		executeUpdate(connection, "truncate t_org");
		System.out.println("清空了表数据");
		insertBatch(connection);
		printRecord(connection, "select count(1) count from t_org");
	}

	private static void insertBatch(final Connection connection)
			throws SQLException {
		long start = System.currentTimeMillis();
		int batchSize = 5000;
		PreparedStatement preparedStatement = connection
				.prepareStatement("INSERT INTO t_org (org_id, org_name, org_parent,org_detail) VALUES (?, ?, ?,?)");
		int executeCount = 0;
		for (long orgId = 1; orgId <= 20000; orgId++) {
			preparedStatement.setObject(1, orgId);
			preparedStatement.setObject(2, "tiny" + orgId);
			preparedStatement.setObject(3, null);
			preparedStatement.setObject(4, "hellohellohellohello" + orgId);
			preparedStatement.addBatch();
			if (orgId % batchSize == 0) {
				preparedStatement.executeBatch();
				executeCount++;
				System.out.println("执行" + batchSize + "条记录，还剩"
						+ (2000000 - (batchSize * executeCount)));
				preparedStatement.clearBatch();
			}
		}
		preparedStatement.executeBatch();
		long end = System.currentTimeMillis();
		System.out.println("执行时间:" + (end - start) + "ms");
	}

	private void insertUserData(final Connection connection)
			throws SQLException {
		for (int orgId = 1; orgId <= 1000000; orgId++) {
			for (int userId = 1; userId <= 5; userId++) {
				executeUpdate(
						connection,
						String.format(
								"INSERT INTO `t_user` (`user_id`, `org_id`, `user_name`,`user_nickname`,`user_age`) VALUES (%s, %s, 'user%s','shuaige%s',%s)",
								userId, orgId, userId, orgId, userId));
			}
		}
	}

	private static void printRecord(final Connection connection, String sql)
			throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
				System.out.println(resultSet.getMetaData().getColumnName(i + 1)
						+ "==" + resultSet.getObject(i + 1));
			}
		}
	}

	private static void printGroupBy(final DataSource dataSource)
			throws SQLException {
		String sql = "SELECT o.user_id, COUNT(*) FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id GROUP BY o.user_id";
		Connection conn = dataSource.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		ResultSet rs = preparedStatement.executeQuery();
		try {
			while (rs.next()) {
				System.out.println("user_id: " + rs.getInt(1) + ", count: "
						+ rs.getInt(2));
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	private static void printHintSimpleSelect(final DataSource dataSource)
			throws SQLException {
		String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id";
		HintManager hintManager = HintManager.getInstance();
		Connection conn = dataSource.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		ResultSet rs = preparedStatement.executeQuery();
		try {
			hintManager.addDatabaseShardingValue("t_order", "user_id", 10);
			hintManager.addTableShardingValue("t_order", "order_id", 1001);
			while (rs.next()) {
				System.out.println(rs.getInt(1));
				System.out.println(rs.getInt(2));
				System.out.println(rs.getInt(3));
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (conn != null) {
				conn.close();
			}
			if (hintManager != null) {
				hintManager.close();
			}
		}

	}

	private static void executeUpdate(final Connection connection,
			final String sql) throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		try {
			preparedStatement.executeUpdate();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}

	private static void executeUpdate(final Connection connection,
			final String sql, int size) throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		try {
			preparedStatement.executeUpdate();
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}

	class BatchInsertThread implements Runnable {

		Connection connection;

		public BatchInsertThread(Connection connection) {
			this.connection = connection;
		}

		@Override
		public void run() {
			int batchSize = 10000;
			PreparedStatement preparedStatement = null;
			try {
				preparedStatement = connection
						.prepareStatement("INSERT INTO `t_org` (`org_id`, `org_name`, `org_parent`,`org_detail`) VALUES (?, ?, ?,?)");
				connection.setAutoCommit(false);
				for (int orgId = 1; orgId <= 1000000; orgId++) {
					preparedStatement.setObject(1, orgId);
					preparedStatement.setObject(2, "tiny" + orgId);
					preparedStatement.setObject(3, null);
					preparedStatement.setObject(4, "hellohellohellohello"
							+ orgId);
					preparedStatement.addBatch();
					if (orgId % batchSize == 0) {
						preparedStatement.executeBatch();
						System.out.println("执行" + batchSize + "条记录");
						preparedStatement.clearBatch();
					}
				}
				preparedStatement.executeBatch();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}
}
